/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import controllers.Event;
import java.sql.Date;

/**
 *
 * @author Dan
 */
public class EventDAO extends DAOTest {

    public static List<Event> getEvents() {
        Connection con = getConnection();
        ArrayList<Event> events = new ArrayList<Event>();
        try {
            CallableStatement cs = con.prepareCall("{call stp_selAllEvents}");
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                int serviceID = rs.getInt("ServiceID");
                int driverID = rs.getInt("DriverID");
                int vehicleID = rs.getInt("VehicleID");
                String notes = rs.getString("Notes");
                Date date = rs.getDate("Date");
                int defaultDriverID = rs.getInt(("DefaultDriverID"));
                System.out.println(notes);
                events.add(new Event(id, serviceID, driverID, vehicleID, notes, date, defaultDriverID));
            }

            cs.close();
            con.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return events;
    }
    
        public static List<Event> getEventsByDate(Date date) {
        Connection con = getConnection();
        ArrayList<Event> events = new ArrayList<Event>();
        try {
            CallableStatement cs = con.prepareCall("{call stp_selEventsByDate(?)}");
            cs.setDate(1, date);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                int serviceID = rs.getInt("ServiceID");
                int driverID = rs.getInt("DriverID");
                int vehicleID = rs.getInt("VehicleID");
                String notes = rs.getString("Notes");
                Date dateEvent = rs.getDate("Date");
                int defaultDriverID = rs.getInt(("DefaultDriverID"));
                events.add(new Event(id, serviceID, driverID, vehicleID, notes, dateEvent, defaultDriverID));
            }

            cs.close();
            con.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return events;
    }


    public static List<Event> getEventsByWeek(Date date) {
        Connection con = getConnection();
        ArrayList<Event> events = new ArrayList<Event>();
        try {
            CallableStatement cs = con.prepareCall("{call stp_selEventsByWeekFromDate(?)}");
            cs.setDate(1, date);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                int serviceID = rs.getInt("ServiceID");
                int driverID = rs.getInt("DriverID");
                int vehicleID = rs.getInt("VehicleID");
                String notes = rs.getString("Notes");
                Date dateEvent = rs.getDate("Date");
                int defaultDriverID = rs.getInt(("DefaultDriverID"));
                events.add(new Event(id, serviceID, driverID, vehicleID, notes, dateEvent, defaultDriverID));
            }

            cs.close();
            con.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return events;
    }

    public static boolean addEvent(Event event) {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_insEvent(?, ?)}");
            cs.setInt(1, event.getServiceID());
            cs.setDate(2, event.getDate());
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    /*
    public static Driver getDriverByID(int driverId) {
    Connection con = getConnection();
    boolean result = false;
    Driver returnDriver = null;

    try {
    CallableStatement cs = con.prepareCall("{call dbo.stp_selDriverByID(?)}");
    cs.setInt(1, driverId);

    ResultSet rs = cs.executeQuery();

    while (rs.next())
    {
    int id = rs.getInt("ID");
    String name = rs.getString("Name");
    String email = rs.getString("EMail");
    String phone = rs.getString("PhoneNumber");
    returnDriver = new Driver(id, name, email, phone);
    }

    cs.close();
    con.close();
    }
    catch (SQLException e) {e.printStackTrace();}
    return returnDriver;
    }
     */
    public synchronized static boolean saveEvent(Event event) {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_updEvent(?, ?, ?, ?, ?, ?, ?)}");
            cs.setInt(1, event.getId());
            cs.setInt(2, event.getServiceID());
            if (event.getDriverID() != 0) {
                cs.setInt(3, event.getDriverID());
            } else {
                cs.setNull(3, java.sql.Types.INTEGER);
            }
            if (event.getVehicleID() != 0) {
                cs.setInt(4, event.getVehicleID());
            } else {
                cs.setNull(4, java.sql.Types.INTEGER);
            }
            cs.setString(5, event.getNotes());
            cs.setDate(6, (java.sql.Date) event.getDate());
            if (event.getDefaultDriverID() != 0) {
                cs.setInt(7, event.getDefaultDriverID());
            } else {
                cs.setNull(7, java.sql.Types.INTEGER);
            }
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }

        return result;
    }
}
